import psycopg2, json
from pm4py import convert_to_bpmn, convert_to_petri_net
from pm4py.objects.bpmn.layout import layouter as bpmn_layouter
from pm4py.objects.bpmn.exporter.variants.etree import get_xml_string
from pm4py.util import constants
from other.merge_and_remove_multiple_states import merge_and_remove_multiple_states
from other.update_parameter import update_parameters


def save_workflow_to_db(bpmn, sequence_json_processed, sequence_json,Workflow_type, database):

    # # 转换 BPMN
    # powl = process_model_obj.get_powl()
    # pn, im, fm = convert_to_petri_net(powl)
    # bpmn = convert_to_bpmn(pn, im, fm)
    # bpmn = bpmn_layouter.apply(bpmn)
    bpmn_data = get_xml_string(bpmn, parameters={"encoding": constants.DEFAULT_ENCODING})




    conn = psycopg2.connect(**database)
    cursor = conn.cursor()

    try:
        # 保存 BPMN + JSON 到 bpmn_sequence(包含中间节点的完整版本)
        cursor.execute("""
            INSERT INTO bpmn_sequence (workflow, bpmn, json_sequence_processed,json_sequence)
            VALUES (%s, %s, %s, %s)
            ON CONFLICT (workflow)
            DO UPDATE SET
                bpmn = EXCLUDED.bpmn,
                json_sequence = EXCLUDED.json_sequence,
                json_sequence_processed=EXCLUDED.json_sequence_processed
        """, (Workflow_type, bpmn_data, json.dumps(sequence_json_processed), json.dumps(sequence_json)))
        conn.commit()

        # 标记阶段
        for item in sequence_json_processed:
            if item.get("now_state") == "start":
                item["next_state"] = Workflow_type

        # 更新参数表
        update_parameters(sequence_json_processed)

        # 删除旧数据
        first_row = sequence_json_processed[0]
        if first_row.get("now_state") == "start":
            first_next_state = first_row.get("next_state")
            raw_condition = first_row.get("condition", "无")
            status = "不需要" if raw_condition == "无" else raw_condition['manuscript_state']
            first_condition_dict = {"status": status, "process_state": True}

            cursor.execute("SELECT id, now_state, next_state, condition FROM workflow_states ORDER BY id;")
            rows = cursor.fetchall()

            delete_ids, found_start = [], False
            for row in rows:
                row_id, now_state, next_state, condition_raw = row
                if not found_start:
                    if now_state == "start" and next_state == first_next_state:
                        found_start = True
                        delete_ids.append(row_id)
                else:
                    if now_state == "start":
                        break
                    delete_ids.append(row_id)

            if delete_ids:
                id_list_str = ",".join([str(i) for i in delete_ids])
                cursor.execute(f"DELETE FROM workflow_states WHERE id IN ({id_list_str});")
                conn.commit()
        #删除中间控制节点并删除重复行
        #sequence_json_processed=merge_and_remove_multiple_states(sequence_json)

        # 插入新数据
        for record in sequence_json_processed:
            now_state = record.get("now_state", "")
            next_state = record.get("next_state", "")
            raw_condition = record.get("condition", "无")
            status = "不需要" if raw_condition == "无" else raw_condition['manuscript_state']
            condition_dict = {"status": status, "process_state": True}

            cursor.execute("""
                INSERT INTO workflow_states (now_state, next_state, condition)
                VALUES (%s, %s, %s);
            """, (now_state, next_state, json.dumps(condition_dict, ensure_ascii=False)))
        conn.commit()

    finally:
        cursor.close()
        conn.close()
